package com.founder.helloworldapp;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.location.Location;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	private static final String name = "/mnt/sdcard/chenwh/location_db.sqlite";
    //private static final String name = "location_db.sqlite"; //数据库名称
    
    private static final int version = 1; //数据库版本

	public DatabaseHelper(Context context) {
		//第三个参数CursorFactory指定在执行查询时获得一个游标实例的工厂类
		//设置为null,代表使用系统默认的工厂类
		super(context, name, null, version);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS track (track_id text primary key, track_name text, info text, begin_date text, end_date text, distance text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS location (location_id integer primary key autoincrement, track_id text, location_time text, provider text, latitude text, longitude text, accuracy text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS google_map_data (lat text, lng text, offset_lat text, offset_lng text)");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
	}
	
	private SQLiteDatabase db;
	
	public void openDb() {
		if (db == null) {
			db = getWritableDatabase();
		}
	}
	
	public void closeDb() {
		if (db != null) {
			db.close();
			db = null;
		}
	}
	
	public void insertTrack(Track track) {
		db.execSQL("insert into track(track_id, track_name, info, begin_date, end_date, distance) values(?,?,?,?,?,?)", 
				new Object[]{track.getTrackId(), track.getTrackName(), track.getInfo(), track.getBeginDate(), track.getEndDate(), track.getDistance()});
	}
	
	public void updateTrackEndDate(long trackId) {
    	db.execSQL("update track set end_date=? where track_id=?", new Object[]{System.currentTimeMillis(), trackId});
	}
	
	public void updateTrack(Track track) {
    	db.execSQL("update track set track_name=?, info=? where track_id=?", new Object[]{track.getTrackName(), track.getInfo(), track.getTrackId()});
	}
	
	public void insertLocation(long trackId, float distance, Location location) {
		db.execSQL("insert into location(track_id, location_time, provider, latitude, longitude, accuracy) values(?,?,?,?,?,?)", 
				new Object[]{trackId, location.getTime(), location.getProvider(), (int) (location.getLatitude() * 1E6), (int) (location.getLongitude() * 1E6), location.getAccuracy()});
		db.execSQL("update track set distance=distance+? where track_id=?", new Object[]{distance, trackId});
	}
	
	public void deleteTrack(long trackId) {
		db.beginTransaction();
		db.execSQL("delete from track where track_id=?", new Object[]{trackId});
		db.execSQL("delete from location where track_id=?", new Object[]{trackId});
		db.setTransactionSuccessful();
		db.endTransaction();
	}
	
	public List<Track> queryTracks() {
        List<Track> tracks = new ArrayList<Track>();
        Cursor cursor = db.rawQuery("select track_id, track_name, info, begin_date, end_date, distance from track order by begin_date desc", null);  
        while (cursor.moveToNext()) {
        	Track track = new Track();
        	track.setTrackId(cursor.getLong(0));
        	track.setTrackName(cursor.getString(1));
        	track.setInfo(cursor.getString(2));
        	track.setBeginDate(cursor.getLong(3));
        	track.setEndDate(cursor.getLong(4));
        	track.setDistance(cursor.getFloat(5));
        	tracks.add(track);
        }
        cursor.close();
        return tracks;  
	}
	
	public List<Location> queryLocations(long trackId, int maxSize) {
        List<Location> locations = new ArrayList<Location>();
        Cursor cursor = db.rawQuery("select count(location_time) cnt from location where track_id=?", new String[]{String.valueOf(trackId)});
        int count = 0;
        if (cursor.moveToNext()) {
        	count = cursor.getInt(0);
        }
        cursor.close();
        if (count == 0) {
            return locations; 
        }
        
        int rowNum = 0;
        int rows = count / maxSize;
        cursor = db.rawQuery("select location_time, provider, latitude, longitude, accuracy from location where track_id=?", new String[]{String.valueOf(trackId)});  
        int lastIndex = cursor.getCount() - 1;
        while (cursor.moveToNext()) {
        	if ((rows <= 1) || (rowNum % rows == 0) || (rowNum == lastIndex)) {
            	Location location = new Location(cursor.getString(1));
            	location.setTime(cursor.getLong(0));
            	location.setLatitude(cursor.getInt(2));
            	location.setLongitude(cursor.getInt(3));
            	location.setAccuracy(cursor.getFloat(4));
            	locations.add(location);
        	}
        	rowNum++;
        }
        cursor.close();
        Log.d("hello", "all count=" + rowNum + ", select count=" + locations.size());
        return locations;  
	}
	
	private static Map<String, Location> offsetCache = new HashMap<String, Location>();
	
	public Location queryOffsetLocation(Location location) {
		String lat = String.valueOf((int)(location.getLatitude() / 1E5));
		String lng = String.valueOf((int)(location.getLongitude() / 1E5));
		String key = lat + "," +lng;
		Location offsetLocation = offsetCache.get(key);
		if (offsetLocation == null) {
	        Cursor cursor = db.rawQuery("SELECT offset_lat, offset_lng FROM google_map_data where lat=? and lng=?", new String[]{lat, lng});  
	        if (cursor.moveToNext()) {
	        	offsetLocation = new Location("");
	        	offsetLocation.setLatitude(cursor.getInt(0));
	        	offsetLocation.setLongitude(cursor.getInt(1));
	        	offsetCache.put(key, offsetLocation);
	        }
	        cursor.close();
		}
		if (offsetLocation != null) {
			location.setLatitude(location.getLatitude() + offsetLocation.getLatitude());
			location.setLongitude(location.getLongitude() + offsetLocation.getLongitude());
		}
        return location;
	}
	
	public void cleanOffsetCache() {
		offsetCache.clear();
	}

}
